﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using CoreLab.Oracle;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.Entities.Addresses
{
    public class AddressExtractorWorker:IWorker
    {
        #region fields
        private Thread _WorkerThread;
        private AddressExtractorInstruction _Instruction;
        private AddressExtractorOutput _Output;
        private Guid _JobTicketID;
        private RunContext _Context;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        #endregion

        #region IWorker Members

        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }

        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }

        public ResultBase Output
        {
            get { return _Output; }
        }

        public Type ComponentType
        {
            get { return typeof(AddressExtractorComponent); }
        }

        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }

        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (AddressExtractorInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (AddressExtractorInstruction)Instruction;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = Thread.CurrentThread;
            this.ParseAddresses();
        }


        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = new Thread(new ThreadStart(this.ParseAddresses));
            this._WorkerThread.Start();
        }

        public void CancelWork()
        {
            if (this._WorkerThread != null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._JobTicketID, this.GetType(), this._Instruction);
                }
            }

        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #endregion

        #region work
        private void ParseAddresses()
        {
            int srcRecCount =
                DbUtil.GetTableRecordCount(this._Instruction.DbProvider,
                                           this._Instruction.ConnStr,
                                           this._Instruction.SelectSql);

            IDbConnection srcConn = null;
            if (this._Instruction.DbProvider == DataProviderType.MSSQL)
                srcConn = new SqlConnection(this._Instruction.ConnStr);
            else
                srcConn = new OracleConnection(this._Instruction.ConnStr);
            IDbConnection tgtConn = null;
            if (this._Instruction.TgtDbProvider == DataProviderType.MSSQL)
                tgtConn = new SqlConnection(this._Instruction.TgtConnStr);
            else
                tgtConn = new OracleConnection(this._Instruction.TgtConnStr);

            try
            {
                TableColumn[] tgtCols =
                    DbUtil.GetTableColumns(
                        this._Instruction.TgtDbProvider, this._Instruction.TgtConnStr, string.Empty,
                        this._Instruction.TgtTable);

                Dictionary<string, TableColumn> fieldCols = new Dictionary<string, TableColumn>();
                Dictionary<string, IDataParameter> parameters = new Dictionary<string, IDataParameter>();
                foreach (TableColumn col in tgtCols)
                {
                    bool add = false;
                    if (col.ColumnName.ToLower() == this._Instruction.AddrIDField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.RefKeyField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.StreetField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.SubDivField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.OrgField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.CityField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.RegionField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.PostalCodeField.ToLower())
                    {
                        add = true;
                    }
                    else if (col.ColumnName.ToLower() == this._Instruction.CountryField.ToLower())
                    {
                        add = true;
                    }

                    if (add)
                    {
                        fieldCols.Add(col.ColumnName, col);
                        if (this._Instruction.TgtDbProvider == DataProviderType.MSSQL)
                        {
                            SqlParameter parameter = new SqlParameter("@" + col.ColumnName, DBNull.Value);
                            parameters.Add(col.ColumnName, parameter);
                        }
                        else
                        {
                            OracleParameter parameter = new OracleParameter("@" + col.ColumnName, DBNull.Value);
                            parameters.Add(col.ColumnName, parameter);
                        }
                    }
                }
                TableColumn[] cols = new TableColumn[fieldCols.Count];
                fieldCols.Values.CopyTo(cols, 0);
                string spName = "BulkInsert_" + this._Instruction.TgtTable + "_SP";
                string spBody =
                    StoredProcUtil.CreateStoredProcedureStatementForInsert(
                        this._Instruction.TgtDbProvider, spName, string.Empty, this._Instruction.TgtTable, cols);
                DbUtil.NewStoredProc(this._Instruction.TgtConnStr, spName, spBody);

                srcConn.Open();
                IDbCommand srcCmd = srcConn.CreateCommand();
                srcCmd.CommandType = CommandType.Text;

                tgtConn.Open();
                IDbCommand tgtCmd = tgtConn.CreateCommand();
                tgtCmd.CommandType = CommandType.StoredProcedure;
                tgtCmd.CommandText = spName;

                srcCmd.CommandText = this._Instruction.SelectSql;
                IDataReader reader = srcCmd.ExecuteReader();
                AddressParser addrParser = new AddressParser();
                int addrID = 0;
                int srcRecProcessed = 0;
                int addrRecCount = 0;
                int usAddrCount = 0;
                int locatableAddrCount = 0;

                while(reader.Read())
                {
                    srcRecProcessed++;
                    if(reader[this._Instruction.FullAddressField]!=null)
                    {
                        string fullAddr = reader[this._Instruction.FullAddressField].ToString();
                        if (fullAddr.Trim().Length > 0)
                        {
                            addrID++;
                            int pkVal = int.Parse(reader[this._Instruction.PKField].ToString());

                            Address addr = addrParser.ParseAddress(addrID, fullAddr);
                            addrRecCount++;
                            if (addr.Country != null)
                            {
                                if (addr.Country == "UNITED STATES OF AMERICA" || addr.Country == "USA" ||
                                    addr.Country == "UNITED STATES")
                                    usAddrCount++;
                            }
                            if (addr.Organization != null || (addr.City != null && addr.Region != null) ||
                                addr.PostalCode != null)
                            {
                                locatableAddrCount++;
                            }

                            tgtCmd.Parameters.Clear();
                            parameters[this._Instruction.AddrIDField].Value = addrID;
                            parameters[this._Instruction.RefKeyField].Value = pkVal;
                            if (!string.IsNullOrEmpty(addr.Street))
                                parameters[this._Instruction.StreetField].Value = addr.Street;
                            else
                                parameters[this._Instruction.StreetField].Value = DBNull.Value;
                            if (!string.IsNullOrEmpty(addr.SubDivision))
                                parameters[this._Instruction.SubDivField].Value = addr.SubDivision;
                            else
                                parameters[this._Instruction.SubDivField].Value = DBNull.Value;
                            if (!string.IsNullOrEmpty(addr.Organization))
                                parameters[this._Instruction.OrgField].Value = addr.Organization;
                            else
                                parameters[this._Instruction.OrgField].Value = DBNull.Value;
                            if (!string.IsNullOrEmpty(addr.City))
                                parameters[this._Instruction.CityField].Value = addr.City;
                            else
                                parameters[this._Instruction.CityField].Value = DBNull.Value;
                            if (!string.IsNullOrEmpty(addr.Region))
                                parameters[this._Instruction.RegionField].Value = addr.Region;
                            else
                                parameters[this._Instruction.RegionField].Value = DBNull.Value;
                            if (!string.IsNullOrEmpty(addr.PostalCode))
                                parameters[this._Instruction.PostalCodeField].Value = addr.PostalCode;
                            else
                                parameters[this._Instruction.PostalCodeField].Value = DBNull.Value;
                            if (!string.IsNullOrEmpty(addr.Country))
                                parameters[this._Instruction.CountryField].Value = addr.Country;
                            else
                                parameters[this._Instruction.CountryField].Value = DBNull.Value;

                            foreach (IDataParameter parameter in parameters.Values)
                                tgtCmd.Parameters.Add(parameter);

                            tgtCmd.ExecuteNonQuery();
                        }
                    }

                    if(this.WorkerStatusChangedEvent !=null && srcRecProcessed % 100==0)
                    {
                        string msg =
                            string.Format(
                                "Parsing addresses, processed {0} of {1} records, " +
                                "found {2} addresses, {3} are US addresses, {4} are locatable",
                                srcRecProcessed, srcRecCount, addrRecCount, usAddrCount, locatableAddrCount);
                        int pct = srcRecProcessed * 100 / srcRecCount;
                        this.WorkerStatusChangedEvent(this._JobTicketID, this.GetType(), this._Instruction,
                            this.WorkflowName, this.ComponentName, msg, pct);
                    }
                }
                reader.Close();

                this._Output =
                    new AddressExtractorOutput(
                        this._Instruction.OwnerComponentID, this._JobTicketID,
                        srcRecCount, addrRecCount, usAddrCount, locatableAddrCount);

                if (this.WorkerFinishedEvent != null)
                    this.WorkerFinishedEvent(this._JobTicketID, this.GetType(), this._Instruction, this._Output);
            }
            catch(Exception ex)
            {
                if (this.WorkerErredEvent != null)
                    this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex.ToString(),true);
            }
            finally
            {
                srcConn.Close();
                tgtConn.Close();
            }
        }
        #endregion
    }
}
